package cn.information.dao;

import cn.information.bean.Product;
import cn.information.utils.DButil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ProductDao {
    public List<Product> findAll(){
        Connection conn = DButil.getConnection ();
        String sql = "select * from product_info";
        Statement stmt = null;
        List<Product> list =new ArrayList<>();
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                Product product = new Product();
                product.setSpid(rs.getInt("spid"));
                product.setGysid(rs.getInt("gysid"));
                product.setSpname(rs.getString("spname"));
                product.setSpprice(rs.getDouble("spprice"));
                product.setSpnumber(rs.getInt("spnumber"));
                product.setSL(rs.getString("SL"));
                product.setSptype(rs.getString("sptype"));
                list.add(product);
            }
        } 
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            DButil.close(conn);
        }
        return list;
        }
        
        public boolean addProduct(Product product) throws SQLException {
            Connection conn = DButil.getConnection();
            String sql = "insert into product_info (spid, gysid,spname, spprice, spnumber, SL, sptype) values (?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement stmt = null;
            try {
                stmt = conn.prepareStatement(sql);
                stmt.setInt(1, product.getSpid());
                stmt.setInt(2, product.getGysid());
                stmt.setString(3, product.getSpname());
                stmt.setDouble(4, product.getSpprice());
                stmt.setInt(5, product.getSpnumber());
                stmt.setString(6, product.getSL());
                stmt.setString(7, product.getSptype());
                int result = stmt.executeUpdate();
                if (result > 0) {
                    return true;
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DButil.close(conn);
            }
            return false;
        }

    public boolean deleteProduct(int id) {
        Connection conn = DButil.getConnection();
        String sql = "delete from product_info where spid=?";
        try {
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            int result = stmt.executeUpdate();
            if (result > 0) {
                return true;
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DButil.close(conn);
        }
        return false;
    }

    public boolean updateProduct(Product product) {
        Connection conn = DButil.getConnection();
        String sql = "update product_info set gysid=?, spname=?, spprice=?, spnumber=?, SL=?, sptype=? where spid=?";
       PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, product.getGysid());
            stmt.setString(2, product.getSpname());
            stmt.setDouble(3, product.getSpprice());
            stmt.setInt(4, product.getSpnumber());
            stmt.setString(5, product.getSL());
            stmt.setString(6, product.getSptype());
            stmt.setInt(7, product.getSpid());
            int result = stmt.executeUpdate();
            if (result > 0) {
                return true;
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DButil.close(conn);
        }
        return false;
    }

    public List<Product> findByType(String type) {
        Connection conn = DButil.getConnection();
        String sql = "select * from product_info where sptype = ?";
        PreparedStatement stmt = null;
        List<Product> list = new ArrayList<>();
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, type);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setSpid(rs.getInt("spid"));
                product.setGysid(rs.getInt("gysid"));
                product.setSpname(rs.getString("spname"));
                product.setSpprice(rs.getDouble("spprice"));
                product.setSpnumber(rs.getInt("spnumber"));
                product.setSL(rs.getString("SL"));
                product.setSptype(rs.getString("sptype"));
                list.add(product);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DButil.close(conn);
        }
        return list;
    }
    public List<Product> findBySpname(String spname) {
        Connection conn = DButil.getConnection();
        String sql = "SELECT * FROM product_info WHERE spname = ?";
        PreparedStatement stmt = null;
        List<Product> list = new ArrayList<>();
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, spname);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setSpid(rs.getInt("spid"));
                product.setGysid(rs.getInt("gysid"));
                product.setSpname(rs.getString("spname"));
                product.setSpprice(rs.getDouble("spprice"));
                product.setSpnumber(rs.getInt("spnumber"));
                product.setSL(rs.getString("SL"));
                product.setSptype(rs.getString("sptype"));
                list.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButil.close(conn);
        }
        return list;
    }
    // 在 ProductDao.java 中添加
    public List<Map<String, Object>> getProductSalesSummary() {
        Connection conn = DButil.getConnection();
        String sql = "SELECT " +
                "  p.spid, " +
                "  p.spname, " +
                "  SUM(pi.spnumber) AS total_sales, " +
                "  SUM(pi.spprice * pi.spnumber) AS total_amount " +
                "FROM purchase_info pi " +
                "JOIN product_info p ON pi.spid = p.spid " +
                "GROUP BY p.spid, p.spname";

        List<Map<String, Object>> result = new ArrayList<>();
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("spid", rs.getInt("spid"));
                row.put("spname", rs.getString("spname"));
                row.put("total_sales", rs.getInt("total_sales"));
                row.put("total_amount", rs.getDouble("total_amount"));
                result.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButil.close(conn);
        }
        return result;
    }
    public Product findSupplierBySpname(String spname) {
        Connection conn = DButil.getConnection();
        String sql = "SELECT * FROM supplier_info WHERE spname = ?";
        Product product = null;
        try {
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, spname);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                product = new Product();
                product.setSpid(rs.getInt("spid"));
                product.setGysid(rs.getInt("gysid"));
                product.setSpname(rs.getString("spname"));
                product.setSpprice(rs.getDouble("spprice"));
                product.setSpnumber(rs.getInt("spnumber"));
                product.setSL(rs.getString("SL"));
                product.setSptype(rs.getString("sptype"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButil.close(conn);
        }
        return product;
    }

}

